﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.ObjectModel;
using System.Data.SqlClient;
using System.Windows.Media.Imaging;
using System.Data;
using System.Globalization;
using System.Collections;

namespace AlgoLogicMessenger
{
    public class DataRowView
    {
        public string qns { get; set; }
        public string answer { get; set; }
        public string picpath { get; set; }
        public string a { get; set; }
        public string b { get; set; }
        public string c { get; set; }
        public string d { get; set; }
        public string m { get; set; }
        public string s { get; set; }
        public string cat { get; set; }

        public DataRowView(System.Data.DataRow r)
        {
            qns = r[0].ToString();
            cat = r[1].ToString();
            answer = r[2].ToString();
            picpath = r[3].ToString();
            a = r[4].ToString();
            b = r[5].ToString();
            c = r[6].ToString();
            d = r[7].ToString();
            m = r[8].ToString();
            s = r[9].ToString();

        }
    }

    public class clsExcelImport
    {
        public string qns { get; set; }
        public string fn { get; set; }
        public string answer { get; set; }
        public string picpath { get; set; }
        public string a { get; set; }
        public string b { get; set; }
        public string c { get; set; }
        public string d { get; set; }
        public string m { get; set; }
        public string s { get; set; }
        public string cat { get; set; }

        public clsExcelImport(System.Data.DataRow r)
        {
            qns = r[0].ToString();
            cat = r[1].ToString();
            fn = r[2].ToString();
            answer = r[3].ToString();
            picpath = r[4].ToString();
            a = r[5].ToString();
            b = r[6].ToString();
            c = r[7].ToString();
            d = r[8].ToString();
            m = r[9].ToString();
            s = r[10].ToString();
        }
    }

    public class clsExcelDel
    {
        public string qns { get; set; }
        public string fn { get; set; }
        public string answer { get; set; }
        public string picpath { get; set; }
        public string a { get; set; }
        public string b { get; set; }
        public string c { get; set; }
        public string d { get; set; }
        public string m { get; set; }
        public string s { get; set; }
        public string cat { get; set; }

        public clsExcelDel(System.Data.DataRow r)
        {
            qns = r[0].ToString();
            cat = r[1].ToString();
            fn = r[2].ToString();
            answer = r[3].ToString();
            picpath = r[4].ToString();
            a = r[5].ToString();
            b = r[6].ToString();
            c = r[7].ToString();
            d = r[8].ToString();
            m = r[9].ToString();
            s = r[10].ToString();
        }
    }

    public class DataAccess
    {
        //SqlConnection conn;
        public DataTable RemoveDuplicateRows(DataTable dtable, string colname)
        {
            Hashtable ht = new Hashtable();
            ArrayList duplicateList = new ArrayList();

            foreach (DataRow drow in dtable.Rows)
            {
                if (ht.Contains(drow[colname]))
                {
                    duplicateList.Add(drow);
                }
                else
                {
                    ht.Add(drow[colname], string.Empty);
                }
            }
            foreach (DataRow dRow in duplicateList)
                dtable.Rows.Remove(dRow);
            return dtable;
        }


        public static void ReImport(clsExcelImport objExcelImport)
        {
            var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
            conn.Open();
            var comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "Insert into dbo.Export Values(@Question, @Category, @FileName, @Answer, @Image, @OptA, @OptB, @OptC, @OptD, @Min, @Sec)";
            comm.Parameters.AddWithValue("@Question", objExcelImport.qns);
            comm.Parameters.AddWithValue("@Category", objExcelImport.cat);
            comm.Parameters.AddWithValue("@FileName", objExcelImport.fn);
            comm.Parameters.AddWithValue("@Answer", objExcelImport.answer);
            comm.Parameters.AddWithValue("@Image", objExcelImport.picpath);
            comm.Parameters.AddWithValue("@OptA", objExcelImport.a);
            comm.Parameters.AddWithValue("@OptB", objExcelImport.b);
            comm.Parameters.AddWithValue("@OptC", objExcelImport.c);
            comm.Parameters.AddWithValue("@OptD", objExcelImport.d);
            comm.Parameters.AddWithValue("@Min", objExcelImport.m);
            comm.Parameters.AddWithValue("@Sec", objExcelImport.s);
            comm.ExecuteNonQuery();

            conn.Close();
        }

        public static void Delete(clsExcelDel objExcelDel)
        {
            var conn = new SqlConnection(@"Data Source=localhost;Initial Catalog=AlgoLogicDB;Integrated Security=True");
            conn.Open();
            var comm = new SqlCommand();
            comm.Connection = conn;
            comm.CommandText = "DELETE FROM dbo.Export";
            comm.ExecuteNonQuery();
        }

    }
}